package com.mx.utils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import com.mx.entity.Person;

public class ExportExcel {
	/**
	 * 字体
	 */
	private static final String FONT_NAME = "Arial";

	/**
	 * <p>
	 * Field DATA: 样式名称
	 * </p>
	 */
	private static final String STYLE_DATA = "data";

	// 导出
	/**
	 * 文件导出
	 * @throws IOException
	 */
	@Test
	public void exportExcel() throws IOException {
		// 声明一个工作薄
		XSSFWorkbook wb = new XSSFWorkbook();
		// 声明一个单子并命名
		XSSFSheet sheet = wb.createSheet("person");
		// 给单子名称一个长度
		sheet.setDefaultColumnWidth((short) 15);
		// 生成一个样式
		Map<String, CellStyle> style = createStyles(wb);
		CellRangeAddress cra = new CellRangeAddress(0, 1, (short) 0, (short) 2);
		sheet.addMergedRegion(cra);
		// 创建第一行（也可以称为表头）
		XSSFRow row1 = sheet.createRow(0);
		XSSFCell cell1 = row1.createCell((short) 0);
		cell1.setCellValue("用户信息");
		cell1.setCellStyle(style.get("header"));

		// 第二行
		XSSFRow row = sheet.createRow(2);
		// 给表头第一行一次创建单元格
		XSSFCell cell = row.createCell((short) 0);
		cell.setCellValue("name");
		cell.setCellStyle(style.get("title"));
		cell = row.createCell((short) 1);
		cell.setCellValue("pwd");
		cell.setCellStyle(style.get("title"));
		cell = row.createCell((short) 2);
		cell.setCellValue("desca");
		cell.setCellStyle(style.get("title"));
		List<Person> list = new ArrayList<Person>();
		list.add(new Person("zhangsan", "123456", "123654"));
		list.add(new Person("lisi", "567656", "656"));
		list.add(new Person("wangwu", "43434", "324234"));
		// 向单元格里填充数据
		for (short i = 0; i < list.size(); i++) {
			row = sheet.createRow(i + 3);
			XSSFCell celld0 = row.createCell(0);
			celld0.setCellValue(list.get(i).getName());
			celld0.setCellStyle(style.get("data2"));
			XSSFCell celld1 = row.createCell(1);
			celld1.setCellValue(list.get(i).getPwd());
			celld1.setCellStyle(style.get("data2"));
			XSSFCell celld2 = row.createCell(2);
			celld2.setCellValue(list.get(i).getDesca());
			celld2.setCellStyle(style.get("data2"));
		}
		// 默认导出到E盘下
		FileOutputStream out = new FileOutputStream("D://学生表.xls");
		wb.write(out);
		out.close();
		JOptionPane.showMessageDialog(null, "导出成功!");
	}

	/**
	 * 创建表格样式
	 * 
	 * @return 样式列表
	 */
	/**
	 * @param HSSFWorkbook 对象
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public Map<String, CellStyle> createStyles(XSSFWorkbook wb) {
		Map<String, CellStyle> stylesMap = new HashMap<String, CellStyle>();

		// 标题样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		Font titleFont = wb.createFont();
		titleFont.setFontName(FONT_NAME);
		titleFont.setFontHeightInPoints((short) 16);
		titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(titleFont);
		stylesMap.put("title", style);

		// 数据样式
		style = wb.createCellStyle();
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
		Font dataFont = wb.createFont();
		dataFont.setFontName(FONT_NAME);
		dataFont.setFontHeightInPoints((short) 10);
		style.setFont(dataFont);
		stylesMap.put(STYLE_DATA, style);

		// 数据样式1
		style = wb.createCellStyle();
		style.cloneStyleFrom(stylesMap.get(STYLE_DATA));
		style.setAlignment(CellStyle.ALIGN_LEFT);
		stylesMap.put("data1", style);

		// 数据样式2
		style = wb.createCellStyle();
		style.cloneStyleFrom(stylesMap.get(STYLE_DATA));
		style.setAlignment(CellStyle.ALIGN_CENTER);
		stylesMap.put("data2", style);

		// 数据样式3
		style = wb.createCellStyle();
		style.cloneStyleFrom(stylesMap.get(STYLE_DATA));
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		stylesMap.put("data3", style);

		// 表头样式
		style = wb.createCellStyle();
		style.cloneStyleFrom(stylesMap.get(STYLE_DATA));
		style.setWrapText(true);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		Font headerFont = wb.createFont();
		headerFont.setFontName(FONT_NAME);
		headerFont.setFontHeightInPoints((short) 10);
		headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		headerFont.setColor(IndexedColors.WHITE.getIndex());
		style.setFont(headerFont);
		stylesMap.put("header", style);

		// 返回
		return stylesMap;
	}
}
